import pymysql
import plotly.express as px

import pandas as pd

class DataBaseLink():
    def __init__(self):
        self.db_config = {
        'host': '192.168.31.134',
        'user': 'root',
        'password': 'Password123@mysql',
        'database': 'oecd_data'
    }
        
    def connect(self):
        self.connection = pymysql.connect(
            host=self.db_config.get('host', 'localhost'),
            user=self.db_config.get('user', 'root'),
            password=self.db_config.get('password', ''),
            database=self.db_config.get('database', 'oecd_data'),
            charset='utf8mb4'
        )

    def show_tables(self):
        self.connect()
        df_tables = pd.read_sql('''show tables''', self.connection)
        self.connection.close()
        return df_tables

    def show_columns(self, table_name):
        self.connect()
        df_columns = pd.read_sql(f'''show columns from {table_name}''', self.connection)
        self.connection.close()
        return df_columns
    
    def show_unique_col(self, table_name, col_name):
        self.connect()
        df_unique = pd.read_sql(f'''select distinct {col_name} from {table_name}''', self.connection)
        self.connection.close()
        return df_unique

if __name__ == "__main__":
    db_link = DataBaseLink()
    df_tables = db_link.show_tables()
    cols = df_tables.iloc[:, 0]
    data_dic = {}
    for col in cols:
        df_columns = db_link.show_columns(col)
        all_cols_name = df_columns.iloc[:, 0].to_list()
        df_columns.index = all_cols_name
        df_columns['unique_values'] = ''
        for i, col_name in enumerate(all_cols_name):
            if col_name == 'obs_value':
                continue
            df_unique = db_link.show_unique_col(col, col_name)
            df_unique_v = str(df_unique.iloc[:, 0].to_list())   
            df_columns.loc[col_name, 'unique_values'] = df_unique_v
            print(df_unique)
        data_dic[col] = df_columns
    with pd.ExcelWriter('OECD数据字典.xlsx') as writer:
        for sheet_name, df in data_dic.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)